package dao;


import bean.RequiredAdvice;
import util.DruidUtil;
import util.DateUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class RequiredAdviceDao {
    Connection conn;
    public RequiredAdviceDao() throws SQLException {
        conn= DruidUtil.getConnection();
    }
    public List<RequiredAdvice> getRequiredAdviceList(String title, int pageNum){
        ArrayList<RequiredAdvice> RequiredAdviceList =new ArrayList<RequiredAdvice>();
        int k=(pageNum-1)*5;
        StringBuffer sql=new StringBuffer();
        sql.append("select * from egov_required_advice where   1=1");
        if(title.length()!=0){
            sql.append("  and  title like ? ");
        }
        sql.append(" limit "+k+",5");
        try {
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            if(title.length()!=0){
                ps.setString(index,"%"+title+"%");
                index++;
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                RequiredAdvice m=new RequiredAdvice();
                m.setId(rs.getInt("id"));
                m.setTitle(rs.getString("title"));
                m.setDescription(rs.getString("description"));
                m.setDepid(rs.getInt("depid"));
                m.setPostdate(rs.getTimestamp("postdate").toString());
                m.setDeadline(rs.getTimestamp("deadline").toString());
                RequiredAdviceList.add(m);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return RequiredAdviceList;
    }
    public int getRequiredAdviceListCount(String title){
        int count=0;
        StringBuffer sql=new StringBuffer();
        sql.append("select count(*) from egov_required_advice  where 1=1");
        if(title.length()!=0){
            sql.append(" and  title like ? ");
        }
        try {
            PreparedStatement ps=conn.prepareStatement(sql.toString());
            int index=1;
            if(title.length()!=0) {
                ps.setString(index,"%"+title+"%");
                index++;
            }
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  count;
    }
    public boolean addRequiredAdvice(RequiredAdvice m)throws Exception{
        StringBuffer sb= new StringBuffer("insert into egov_required_advice(title,description,depid,Postdate,deadline)  values(?, ? , ?,current_timestamp() , ? ) ");
        PreparedStatement ps=conn.prepareStatement(sb.toString());
        ps.setString(1,m.getTitle());
        ps.setString(2,m.getDescription());
        ps.setInt(3,m.getDepid());
        ps.setTimestamp(4,new java.sql.Timestamp(DateUtil.parse(m.getDeadline()).getTime()));

       if (ps.execute()){
           return true;
       }
       return false;
    }

    public RequiredAdvice getRequiredAdviceById(int id){
        RequiredAdvice m=new RequiredAdvice();
        String sql="select * from egov_required_advice where  id = ? ";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
               m.setId(rs.getInt("id"));
               m.setTitle(rs.getString("title"));
               m.setDescription(rs.getString("description"));
               m.setDepid(rs.getInt("depid"));
               m.setPostdate(rs.getTimestamp("Postdate").toString());
               m.setDeadline(rs.getTimestamp("deadline").toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  m;
    }
}
